package com.css.fxfzcpxx.modules.earthquakeDisaster.repository.impl;

import com.css.fxfzcpxx.constants.YNEnum;
import com.css.fxfzcpxx.modules.categoryView.param.QueParam;
import com.css.fxfzcpxx.modules.categoryView.vo.CategoryViewVo;
import com.css.fxfzcpxx.modules.earthquakeDisaster.entity.EarthquakeDisaster;
import com.css.fxfzcpxx.modules.earthquakeDisaster.param.EarthquakeDisasterParam;
import com.css.fxfzcpxx.modules.earthquakeDisaster.repository.EarthquakeDisasterRepository;
import com.css.fxfzcpxx.modules.infoRetrieval.historicalSeismicInfo.param.FindByParam;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/02/28
 * @Description: 地震灾害信息
 */
@Repository
public class EarthquakeDisasterRepositoryImpl implements EarthquakeDisasterRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "cpxx_earthquake_damage_survey";

    private static final String TABLE_ID = "jeds_id"; // 表id字段名

    //分页
    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, EarthquakeDisasterParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by jeds_date desc");
        int firstIndex = (curPage - 1) * pageSize;
        String limitSql = String.format(" limit %d  offset %d ", pageSize, firstIndex);
        //StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //时间范围
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & !PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and date(jeds_date) between '" + param.getStartTime() + "' and '" + param.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and jeds_date  >='" + param.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(param.getEndTime()) & PlatformObjectUtils.isEmpty(param.getStartTime())) {
            whereSql.append("and jeds_date  <='" + param.getEndTime() + "'");
        }
        //省
        if (!PlatformObjectUtils.isEmpty(param.getProvince())) {
            whereSql.append(" and province = '" + param.getProvince() + "'");
        }
        //市
        if (!PlatformObjectUtils.isEmpty(param.getCity())) {
            whereSql.append(" and city = '" + param.getCity() + "'");
        }
        //县
        if (!PlatformObjectUtils.isEmpty(param.getCounty())) {
            whereSql.append(" and county = '" + param.getCounty() + "'");
        }
        //乡/街道
        if (!PlatformObjectUtils.isEmpty(param.getTown())) {
            whereSql.append(" and town = '" + param.getTown() + "'");
        }
        //震级范围大于等于
        if (!PlatformObjectUtils.isEmpty(param.getStartMagnitude())) {
            whereSql.append(" and CAST(magnitude  as NUMERIC ) >= CAST('" + param.getStartMagnitude() + "' as NUMERIC ) ");
        }
        //震级范围小于等于
        if (!PlatformObjectUtils.isEmpty(param.getEndMagnitude())) {
            whereSql.append(" and CAST(magnitude  as NUMERIC ) <= CAST('" + param.getEndMagnitude() + "' as NUMERIC ) ");
        }
        //发布状态
        if (!PlatformObjectUtils.isEmpty(param.getReleaseStatus())) {
            whereSql.append(" and release_status = '" + param.getReleaseStatus() + "'");
        }

        //经度范围
        if (!PlatformObjectUtils.isEmpty(param.getJedsLongitude()) & !PlatformObjectUtils.isEmpty(param.getJedsLongitude1())) {
            whereSql.append("and jeds_longitude between '" + param.getJedsLongitude() + "' and '" + param.getJedsLongitude1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getJedsLongitude()) & PlatformObjectUtils.isEmpty(param.getJedsLongitude1())) {
            whereSql.append("and jeds_longitude  >='" + param.getJedsLongitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getJedsLongitude1()) & PlatformObjectUtils.isEmpty(param.getJedsLongitude())) {
            whereSql.append("and jeds_longitude  <='" + param.getJedsLongitude1() + "'");
        }

        //纬度范围
        if (!PlatformObjectUtils.isEmpty(param.getJedsLatitude()) & !PlatformObjectUtils.isEmpty(param.getJedsLatitude1())) {
            whereSql.append("and jeds_latitude between '" + param.getJedsLatitude() + "' and '" + param.getJedsLatitude1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getJedsLatitude()) & !PlatformObjectUtils.isEmpty(param.getJedsLatitude1())) {
            whereSql.append("and jeds_latitude  >='" + param.getJedsLatitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getJedsLatitude1()) & !PlatformObjectUtils.isEmpty(param.getJedsLatitude())) {
            whereSql.append("and jeds_latitude  <='" + param.getJedsLatitude1() + "'");
        }

        if (!PlatformObjectUtils.isEmpty(param.getLongitude()) & PlatformObjectUtils.isEmpty(param.getLongitude1())) {
            whereSql.append("and jeds_longitude  >='" + param.getLongitude() + "'");
        }else if (PlatformObjectUtils.isEmpty(param.getLongitude()) & !PlatformObjectUtils.isEmpty(param.getLongitude1())) {
            whereSql.append("and jeds_longitude  <='" + param.getLongitude1() + "'");
        }else if (!PlatformObjectUtils.isEmpty(param.getLongitude()) & !PlatformObjectUtils.isEmpty(param.getLongitude1())) {
            whereSql.append("and (jeds_longitude  >='" + param.getLongitude() + "' and jeds_longitude<='" + param.getLongitude1() + "')");
        }

        if (!PlatformObjectUtils.isEmpty(param.getLatitude()) & PlatformObjectUtils.isEmpty(param.getLatitude1())) {
            whereSql.append("and jeds_latitude  >='" + param.getLatitude() + "'");
        }else if (PlatformObjectUtils.isEmpty(param.getLatitude()) & !PlatformObjectUtils.isEmpty(param.getLatitude1())) {
            whereSql.append("and jeds_latitude  <='" + param.getLatitude1() + "'");
        }else if (!PlatformObjectUtils.isEmpty(param.getLatitude()) & !PlatformObjectUtils.isEmpty(param.getLatitude1())) {
            whereSql.append("and (jeds_latitude  >='" + param.getLatitude() + "' and jeds_latitude<='" + param.getLatitude1() + "')");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EarthquakeDisaster.class)).size();
        List<EarthquakeDisaster> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(EarthquakeDisaster.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //修改发布状态
    @Override
    public void updateReleaseStatusById(List<String> id, String toString, Date date) {
        String sql = "update " + TABLE_NAME + " set release_status = '" + toString + "',release_date = '"+date+"' where " + TABLE_ID + " in  ('" + String.join("','", id) + "')";
        jdbcTemplate.update(sql);
    }

    //根据id删除
    @Override
    public void deleteById(String id) {
        String sql = "update " + TABLE_NAME + " set del_flag = ?  where " + TABLE_ID + " = ?";
        jdbcTemplate.update(sql, YNEnum.Y.toString(), id);
    }

    //根据id查询单条数据
    @Override
    public EarthquakeDisaster findById(String id) {
        String sql = "select * from " + TABLE_NAME + " where " + TABLE_ID + " = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EarthquakeDisaster.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //根据id修改
    @Override
    public void updateById(EarthquakeDisaster param) {
        String sql = "update " + TABLE_NAME +
                " set event_abbreviation = :eventAbbreviation,jeds_date = :jedsDate," +
                "jeds_time = :jedsTime,magnitude = :magnitude," +
                "jeds_longitude = :jedsLongitude,jeds_latitude = :jedsLatitude," +
                "depth = :depth,note = :note," +
                "location1 = :location1,dead_number = :deadNumber," +
                "injuries = :injuries,maximum_intensity = :maximumIntensity," +
                "cause_of_death = :causeOfDeath,des_earthquake_damage = :desEarthquakeDamage," +
                "attachment = :attachment,isoline_pic = :isolinePic," +
                "intensity_map = :intensityMap,jeds_zhzp = :jedsZhzp," +
                "update_user = :updateUser,update_time = :updateTime " +
                "where del_flag = '0' and " + TABLE_ID + " = '" + param.getJedsId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //根据参数查询
    @Override
    public List<EarthquakeDisaster> queryByParam(List<String> id) {
        String sql = "select * from "+TABLE_NAME+" where "+TABLE_ID+" in  ('" + String.join("','", id) + "')";
        List<EarthquakeDisaster> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EarthquakeDisaster.class));
        return list;
    }

    @Override
    public void batchSave(List<EarthquakeDisaster> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO "+TABLE_NAME+" (jeds_id,event_abbreviation,reference_position,jeds_date,jeds_time," +
                "location,location1,jeds_longitude,jeds_latitude,magnitude,depth,maximum_intensity,dead_number,cause_of_death," +
                "injuries,des_earthquake_damage,note,release_status,del_flag,create_user,create_time,province,city,county)" +
                "values (:jedsId,:eventAbbreviation,:referencePosition,:jedsDate,:jedsTime," +
                ":location,:location1,:jedsLongitude,:jedsLatitude,:magnitude,:depth,:maximumIntensity,:deadNumber,:causeOfDeath," +
                ":injuries,:desEarthquakeDamage,:note,:releaseStatus,:delFlag,:createUser,:createTime,:province,:city,:county)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void save(EarthquakeDisaster earthquakeDisaster) {
        String sql = "INSERT INTO "+TABLE_NAME+" (jeds_id,event_abbreviation,reference_position,jeds_date,jeds_time," +
                "location,location1,jeds_longitude,jeds_latitude,magnitude,depth,maximum_intensity,dead_number,cause_of_death," +
                "injuries,des_earthquake_damage,note,release_status,del_flag,create_user,create_time,province,city,county)" +
                "values (:jedsId,:eventAbbreviation,:referencePosition,:jedsDate,:jedsTime," +
                ":location,:location1,:jedsLongitude,:jedsLatitude,:magnitude,:depth,:maximumIntensity,:deadNumber,:causeOfDeath," +
                ":injuries,:desEarthquakeDamage,:note,:releaseStatus,:delFlag,:createUser,:createTime,:province,:city,:county)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(earthquakeDisaster);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public EarthquakeDisaster findByParam(FindByParam param) {
        String sql = "select * from " + TABLE_NAME + " where " +
                " jeds_date = '" + param.getOccurrenceDate() + "' and jeds_time ='" + param.getOccurrenceTime() + "' " +
                " and jeds_longitude = '" + param.getLongitude() + "' and jeds_latitude ='" + param.getLatitude() + "' " +
                " and magnitude = '" + param.getMagnitude() + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EarthquakeDisaster.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<CategoryViewVo> getData(QueParam queParam) {
        StringBuilder sql = new StringBuilder("select event_abbreviation meansName,data_origin,release_date publishingDate," +
                "create_time,jeds_id id from "+TABLE_NAME+"");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"' and release_status='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParam.getKeyword())) {
            whereSql.append(" and event_abbreviation like '%" + queParam.getKeyword() + "%'");
        }

        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & !PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date between '" + queParam.getStartTime() + "' and '" + queParam.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date  >='" + queParam.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParam.getEndTime()) & PlatformObjectUtils.isEmpty(queParam.getStartTime())) {
            whereSql.append("and release_date  <='" + queParam.getEndTime() + "'");
        }
        List<CategoryViewVo> list = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(CategoryViewVo.class));
        return list;
    }

    @Override
    public void updateAttachId(String attachId, String column, String jedsId) {
        String sql = "update " + TABLE_NAME + " set " + column + " = '" + attachId + "' where jeds_id = '" + jedsId + "'";
        jdbcTemplate.update(sql);
    }
}
